from datetime import datetime
from IPython.display import display
from IPython.display import Markdown as MD
current_date = datetime.now().strftime('%Y-%m-%d')
version = datetime.now().strftime('%Y%m%d.%H%M')
display(MD(f"**Date:** {current_date}"))
display(MD(f"**version:** {version}"))

# this code to will import all the things i need for this notebook

import os
import re
import math

import pdfplumber

import numpy as np
import pandas as pd

# for the notebook rendering 
from IPython.display import display, HTML
from IPython.display import Markdown as MD


# Graphs and Charts
import matplotlib.pyplot as plt
from matplotlib.colors import Normalize
import seaborn as sns
import plotly.express as px
# use to export plotly graphs
import plotly.io as pio 

#misc
from scipy.stats import spearmanr, kendalltau
import pycountry

# pandas Settings/Options
pd.set_option("display.max_rows", None) 
pd.set_option("display.max_columns", None)
pd.set_option('display.width', 9000)
pd.set_option('max_colwidth', 400)
pd.set_option('display.float_format', '{:.3f}'.format)

# colormap 
heatmapCM = sns.color_palette('Spectral_r', as_cmap=True)


## directories 
DIR = os.getcwd()
print(f'{DIR=}')

DataDIR = os.path.join(DIR,'data')
OutDIR = os.path.join(DIR,'docs')

if not os.path.exists(DataDIR):
    print('***DATA FOLDER IS MISSING***')

if not os.path.exists(OutDIR):
    os.makedirs(OutDIR)

DIR='C:\\Users\\JGarza\\GitHub\\Fred_And_The_Vaccines'

df = pd.read_csv(
    os.path.join(DataDIR,'Table 2025-02-10 02.26.07.csv'),
    skiprows=2,
    header=[0]
    )

# display(df.tail(1))

df = df.rename(columns={"Year: ":"County"})
for c in df.columns:
    df = df.rename(columns={c:c.strip()})
    

bad_rows= ['Statistics: ','County ','Total for selection ','Missouri ','Source:','Generated On:']
df = df[~df["County"].isin(bad_rows)]

df = df.drop(columns=['Total for selection','Unnamed: 10'])

for i in range(2015,2025):
    try:
        df[str(i)] = df[str(i)].astype(int)
    except:
        pass

df = df.sort_values(by='2021',ascending=False)

display(df.style.background_gradient(cmap=heatmapCM,axis=1))

# normalizing the values
dfn = df.melt(id_vars=["County"], var_name="year", value_name="value")

dfn['value'] = dfn['value'].astype(float,errors='ignore')
dfn = dfn[dfn.value != ' ']

## baseline years are 2015,2016,2017 before the pandemic 
blyears = dfn[dfn.year.isin(['2015','2016','2017'])]
grouped = blyears.groupby('County')

temp = grouped['value'].agg(
    baseline='mean',
).reset_index()

for _, row in temp.iterrows():
    try:
        dfn.loc[dfn.County == row['County'], 'value'] = dfn[(dfn.County == row['County'])].value / row['baseline']
    except:
        pass

dfn = pd.pivot_table(
    data = dfn.copy(),
    values = 'value',
    index = ['County'],
    columns=['year'],
    aggfunc='mean',
)
dfn.columns.name = 'index' 
dfn = dfn.reset_index()

for i in range(2015,2025):
    try:
        dfn[str(i)] = dfn[str(i)].astype(float)
    except:
        pass

display(MD('### HeatMap - Normalized Deaths 2015-2021 of Missouri Countys (based on Year)'))
# dfn = dfn.sort_values(by='2021',ascending=False)
display(dfn.style.background_gradient(cmap=heatmapCM,axis=1))

# import Vaccine Data 
df = pd.read_csv(os.path.join(DataDIR,'LNU01074597.csv'))

df = df.rename(columns={'observation_date':'date'})
df = df.rename(columns={'LNU01074597':'count'})

display(df.head(5))

title = 'Civilian Labor Force - With a Disability, 16 Years and over'
display(MD(f'### {title}'))
display(MD(f'#### min & max lines calculated with values before 2020-01-01'))

fig = px.line(
    df,
    x='date',
    y='count',
    height=750 ,
    title=title
    )
fig.update_layout(template="plotly_dark")

temp = df[df['date'] < '2020-01-01'].copy()
min_temp = temp['count'].min()
max_temp = temp['count'].max()

# adding Min Line
fig.add_shape(
    type="line",
    x0='2009-01-01', 
    x1='2024-01-01', 
    y0=min_temp,
    y1=min_temp, 
    line=dict(color="Grey", width=2, dash="dash"),  # Line style
    xref="x", yref="y"          # Reference axes
)

# adding Max Line
fig.add_shape(
    type="line",
    x0='2009-01-01', 
    x1='2024-01-01', 
    y0=max_temp,
    y1=max_temp, 
    line=dict(color="Grey", width=2, dash="dash"),  # Line style
    xref="x", yref="y"          # Reference axes
)

fig.add_shape(
    type="circle",
    x0='2020-11-01', x1='2021-03-01',  # Keep x values the same for a point
    y0=5846-100, y1=5846+100,  # Define the vertical range
    line=dict(color="Red", width=2),  # Circle border style
    xref="x", yref="y"
)


fig.show()

	County	2015	2016	2017	2018	2019	2020	2021
103	St. Louis County	9970	10002	10263	10876	10313	12502	11370
49	Jackson	6320	6142	6476	6366	6467	7537	7850
99	St. Charles	2744	2839	2998	3148	3125	3792	3662
40	Greene	2859	2875	2975	2954	2999	3417	3581
102	St. Louis City	2967	3016	3130	3035	3022	3666	3423
51	Jefferson	1980	2070	2147	2181	2175	2469	2527
25	Clay	1811	1703	1820	1852	1898	2217	2245
50	Jasper	1108	1197	1289	1262	1238	1506	1543
11	Boone	1050	1039	1084	1237	1204	1401	1392
37	Franklin	1021	1055	1004	1072	1147	1279	1334
20	Cass	883	932	1002	1029	1016	1119	1258
12	Buchanan	948	897	987	957	924	1131	1117
101	St. Francois	779	747	797	808	782	973	1028
23	Christian	676	596	700	684	734	855	948
17	Cape Girardeau	774	863	767	820	837	1079	928
84	Platte	671	691	750	747	744	877	907
74	Newton	648	642	664	694	661	747	818
108	Taney	615	590	650	600	597	718	796
27	Cole	645	680	624	711	716	882	779
13	Butler	628	509	585	608	568	688	708
16	Camden	492	496	469	539	518	646	664
47	Howell	535	494	501	530	546	638	640
58	Lincoln	504	502	487	502	522	562	607
82	Phelps	480	494	475	430	482	591	602
96	Scott	423	461	420	469	460	563	583
56	Lawrence	442	479	498	515	498	546	580
54	Laclede	412	425	447	459	462	496	564
52	Johnson	448	442	400	398	420	494	562
36	Dunklin	425	423	459	473	414	558	519
81	Pettis	435	445	495	482	445	589	517
114	Webster	355	326	355	393	366	435	507
15	Callaway	397	399	399	458	424	519	501
6	Barry	426	439	439	455	413	523	501
106	Stone	360	404	409	444	424	454	498
55	Lafayette	399	393	405	413	384	460	470
111	Warren	289	300	322	324	296	373	463
86	Pulaski	311	320	330	339	344	407	459
105	Stoddard	432	383	402	407	385	454	449
109	Texas	321	297	309	331	330	394	440
85	Polk	337	329	419	374	382	420	428
112	Washington	294	301	313	273	305	372	398
64	Marion	343	340	354	341	347	374	396
29	Crawford	287	324	319	315	322	361	387
67	Miller	317	266	286	294	297	347	366
9	Benton	306	297	309	326	317	367	364
89	Randolph	289	295	294	279	301	342	358
90	Ray	235	281	287	277	247	339	354
43	Henry	335	282	297	301	308	363	340
110	Vernon	274	237	258	237	243	281	339
5	Audrain	298	299	328	310	314	389	330
72	Morgan	283	336	265	312	287	338	327
93	Saline	237	255	271	264	251	315	326
65	McDonald	236	227	254	244	250	307	303
31	Dallas	201	205	201	213	214	305	302
73	New Madrid	226	249	239	273	250	337	300
79	Pemiscot	245	237	231	241	222	303	294
116	Wright	210	246	292	272	246	232	292
2	Adair	212	209	244	213	247	241	290
34	Dent	195	213	191	174	227	268	269
38	Gasconade	215	194	221	212	226	271	248
104	Ste. Genevieve	198	192	196	216	183	206	247
26	Clinton	254	250	267	261	231	313	246
8	Bates	208	209	229	185	217	225	245
113	Wayne	199	187	199	213	158	223	242
21	Cedar	225	214	192	222	223	187	236
92	Ripley	200	169	200	190	194	220	236
35	Douglas	179	183	176	206	180	205	229
61	Macon	203	183	197	215	212	268	227
28	Cooper	190	213	187	200	185	203	227
80	Perry	193	218	186	219	194	258	225
68	Mississippi	156	170	189	196	175	219	221
60	Livingston	197	202	173	165	186	201	220
62	Madison	175	164	179	176	148	210	217
83	Pike	209	204	211	213	195	230	212
44	Hickory	158	153	151	152	146	161	199
3	Andrew	162	178	155	159	172	219	193
48	Iron	162	146	165	143	157	185	189
78	Ozark	119	129	133	139	111	160	187
59	Linn	185	173	177	184	151	194	186
71	Montgomery	154	163	194	150	193	195	185
76	Oregon	151	151	145	159	133	165	179
69	Moniteau	141	152	142	175	149	199	174
7	Barton	159	138	141	122	153	155	171
75	Nodaway	200	186	199	213	178	201	168
77	Osage	123	122	141	147	136	145	165
10	Bollinger	130	142	158	144	118	186	162
57	Lewis	129	121	124	123	108	126	155
100	St. Clair	161	151	143	117	142	165	153
41	Grundy	155	139	112	127	125	152	152
46	Howard	94	100	118	116	106	109	143
88	Ralls	106	110	90	86	105	146	137
14	Caldwell	94	99	103	117	105	120	135
63	Maries	101	106	107	94	113	152	134
33	DeKalb	114	116	133	160	102	157	131
70	Monroe	98	106	101	103	114	127	122
97	Shannon	86	89	100	90	123	141	120
30	Dade	104	107	101	116	116	135	119
22	Chariton	98	88	97	96	93	133	118
91	Reynolds	94	72	78	99	98	95	112
18	Carroll	107	117	114	121	107	139	111
42	Harrison	115	119	127	109	106	123	110
32	Daviess	102	96	83	85	84	112	107
39	Gentry	75	92	104	96	87	108	105
19	Carter	83	86	81	92	87	92	102
24	Clark	90	86	71	81	77	99	102
4	Atchison	81	74	70	71	87	95	95
87	Putnam	60	80	74	61	87	72	89
107	Sullivan	66	57	79	83	68	84	87
98	Shelby	86	85	98	67	75	97	87
45	Holt	67	57	56	54	59	72	83
95	Scotland	75	66	49	41	47	61	68
53	Knox	47	50	37	56	39	49	67
94	Schuyler	59	46	51	58	50	60	64
66	Mercer	43	51	41	54	36	70	52
115	Worth	31	37	35	25	28	29	32

index	County	2015	2016	2017	2018	2019	2020	2021
0	Adair	0.956391	0.942857	1.100752	0.960902	1.114286	1.087218	1.308271
1	Andrew	0.981818	1.078788	0.939394	0.963636	1.042424	1.327273	1.169697
2	Atchison	1.080000	0.986667	0.933333	0.946667	1.160000	1.266667	1.266667
3	Audrain	0.966486	0.969730	1.063784	1.005405	1.018378	1.261622	1.070270
4	Barry	0.980061	1.009969	1.009969	1.046779	0.950153	1.203221	1.152607
5	Barton	1.089041	0.945205	0.965753	0.835616	1.047945	1.061644	1.171233
6	Bates	0.965944	0.970588	1.063467	0.859133	1.007740	1.044892	1.137771
7	Benton	1.006579	0.976974	1.016447	1.072368	1.042763	1.207237	1.197368
8	Bollinger	0.906977	0.990698	1.102326	1.004651	0.823256	1.297674	1.130233
9	Boone	0.992751	0.982351	1.024898	1.169556	1.138355	1.324614	1.316105
10	Buchanan	1.004237	0.950212	1.045551	1.013771	0.978814	1.198093	1.183263
11	Butler	1.094077	0.886760	1.019164	1.059233	0.989547	1.198606	1.233449
12	Caldwell	0.952703	1.003378	1.043919	1.185811	1.064189	1.216216	1.368243
13	Callaway	0.996653	1.001674	1.001674	1.149791	1.064435	1.302929	1.257741
14	Camden	1.013040	1.021277	0.965683	1.109815	1.066575	1.330130	1.367193
15	Cape Girardeau	0.965890	1.076955	0.957155	1.023295	1.044509	1.346506	1.158070
16	Carroll	0.949704	1.038462	1.011834	1.073964	0.949704	1.233728	0.985207
17	Carter	0.996000	1.032000	0.972000	1.104000	1.044000	1.104000	1.224000
18	Cass	0.940362	0.992545	1.067093	1.095847	1.082002	1.191693	1.339723
19	Cedar	1.069731	1.017433	0.912837	1.055468	1.060222	0.889065	1.122029
20	Chariton	1.038869	0.932862	1.028269	1.017668	0.985866	1.409894	1.250883
21	Christian	1.028398	0.906694	1.064909	1.040568	1.116633	1.300710	1.442191
22	Clark	1.093117	1.044534	0.862348	0.983806	0.935223	1.202429	1.238866
23	Clay	1.018560	0.957818	1.023622	1.041620	1.067492	1.246907	1.262655
24	Clinton	0.988327	0.972763	1.038911	1.015564	0.898833	1.217899	0.957198
25	Cole	0.992817	1.046691	0.960493	1.094407	1.102104	1.357619	1.199076
26	Cooper	0.966102	1.083051	0.950847	1.016949	0.940678	1.032203	1.154237
27	Crawford	0.925806	1.045161	1.029032	1.016129	1.038710	1.164516	1.248387
28	Dade	1.000000	1.028846	0.971154	1.115385	1.115385	1.298077	1.144231
29	Dallas	0.993410	1.013180	0.993410	1.052718	1.057661	1.507414	1.492586
30	Daviess	1.088968	1.024911	0.886121	0.907473	0.896797	1.195730	1.142349
31	DeKalb	0.942149	0.958678	1.099174	1.322314	0.842975	1.297521	1.082645
32	Dent	0.976628	1.066778	0.956594	0.871452	1.136895	1.342237	1.347245
33	Douglas	0.998141	1.020446	0.981413	1.148699	1.003717	1.143123	1.276952
34	Dunklin	0.975516	0.970926	1.053558	1.085692	0.950268	1.280796	1.191278
35	Franklin	0.994481	1.027597	0.977922	1.044156	1.117208	1.245779	1.299351
36	Gasconade	1.023810	0.923810	1.052381	1.009524	1.076190	1.290476	1.180952
37	Gentry	0.830258	1.018450	1.151292	1.062731	0.963100	1.195572	1.162362
38	Greene	0.984843	0.990355	1.024802	1.017568	1.033069	1.177058	1.233551
39	Grundy	1.145320	1.027094	0.827586	0.938424	0.923645	1.123153	1.123153
40	Harrison	0.955679	0.988920	1.055402	0.905817	0.880886	1.022161	0.914127
41	Henry	1.099562	0.925602	0.974836	0.987965	1.010941	1.191466	1.115974
42	Hickory	1.025974	0.993506	0.980519	0.987013	0.948052	1.045455	1.292208
43	Holt	1.116667	0.950000	0.933333	0.900000	0.983333	1.200000	1.383333
44	Howard	0.903846	0.961538	1.134615	1.115385	1.019231	1.048077	1.375000
45	Howell	1.049020	0.968627	0.982353	1.039216	1.070588	1.250980	1.254902
46	Iron	1.027484	0.926004	1.046512	0.906977	0.995772	1.173362	1.198732
47	Jackson	1.001162	0.972964	1.025874	1.008449	1.024448	1.193949	1.243532
48	Jasper	0.924875	0.999165	1.075960	1.053422	1.033389	1.257095	1.287980
49	Jefferson	0.958528	1.002098	1.039374	1.055833	1.052929	1.195256	1.223334
50	Johnson	1.041860	1.027907	0.930233	0.925581	0.976744	1.148837	1.306977
51	Knox	1.052239	1.119403	0.828358	1.253731	0.873134	1.097015	1.500000
52	Laclede	0.962617	0.992991	1.044393	1.072430	1.079439	1.158879	1.317757
53	Lafayette	1.000000	0.984962	1.015038	1.035088	0.962406	1.152882	1.177945
54	Lawrence	0.934461	1.012685	1.052854	1.088795	1.052854	1.154334	1.226216
55	Lewis	1.034759	0.970588	0.994652	0.986631	0.866310	1.010695	1.243316
56	Lincoln	1.012726	1.008707	0.978567	1.008707	1.048895	1.129270	1.219692
57	Linn	1.037383	0.970093	0.992523	1.031776	0.846729	1.087850	1.042991
58	Livingston	1.033217	1.059441	0.907343	0.865385	0.975524	1.054196	1.153846
59	Macon	1.044597	0.941681	1.013722	1.106346	1.090909	1.379074	1.168096
60	Madison	1.013514	0.949807	1.036680	1.019305	0.857143	1.216216	1.256757
61	Maries	0.964968	1.012739	1.022293	0.898089	1.079618	1.452229	1.280255
62	Marion	0.992285	0.983607	1.024108	0.986500	1.003857	1.081967	1.145612
63	McDonald	0.987448	0.949791	1.062762	1.020921	1.046025	1.284519	1.267782
64	Mercer	0.955556	1.133333	0.911111	1.200000	0.800000	1.555556	1.155556
65	Miller	1.094361	0.918297	0.987342	1.014960	1.025316	1.197929	1.263521
66	Mississippi	0.908738	0.990291	1.100971	1.141748	1.019417	1.275728	1.287379
67	Moniteau	0.972414	1.048276	0.979310	1.206897	1.027586	1.372414	1.200000
68	Monroe	0.963934	1.042623	0.993443	1.013115	1.121311	1.249180	1.200000
69	Montgomery	0.904110	0.956947	1.138943	0.880626	1.133072	1.144814	1.086106
70	Morgan	0.960407	1.140271	0.899321	1.058824	0.973982	1.147059	1.109729
71	New Madrid	0.949580	1.046218	1.004202	1.147059	1.050420	1.415966	1.260504
72	Newton	0.994882	0.985670	1.019447	1.065507	1.014841	1.146878	1.255885
73	Nodaway	1.025641	0.953846	1.020513	1.092308	0.912821	1.030769	0.861538
74	Oregon	1.013423	1.013423	0.973154	1.067114	0.892617	1.107383	1.201342
75	Osage	0.955959	0.948187	1.095855	1.142487	1.056995	1.126943	1.282383
76	Ozark	0.937008	1.015748	1.047244	1.094488	0.874016	1.259843	1.472441
77	Pemiscot	1.030856	0.997195	0.971950	1.014025	0.934081	1.274895	1.237027
78	Perry	0.969849	1.095477	0.934673	1.100503	0.974874	1.296482	1.130653
79	Pettis	0.949091	0.970909	1.080000	1.051636	0.970909	1.285091	1.128000
80	Phelps	0.993789	1.022774	0.983437	0.890269	0.997930	1.223602	1.246377
81	Pike	1.004808	0.980769	1.014423	1.024038	0.937500	1.105769	1.019231
82	Platte	0.953125	0.981534	1.065341	1.061080	1.056818	1.245739	1.288352
83	Polk	0.931797	0.909677	1.158525	1.034101	1.056221	1.161290	1.183410
84	Pulaski	0.970864	0.998959	1.030177	1.058273	1.073881	1.270552	1.432882
85	Putnam	0.841121	1.121495	1.037383	0.855140	1.219626	1.009346	1.247664
86	Ralls	1.039216	1.078431	0.882353	0.843137	1.029412	1.431373	1.343137
87	Randolph	0.987472	1.007973	1.004556	0.953303	1.028474	1.168565	1.223235
88	Ray	0.877958	1.049813	1.072229	1.034869	0.922790	1.266501	1.322540
89	Reynolds	1.155738	0.885246	0.959016	1.217213	1.204918	1.168033	1.377049
90	Ripley	1.054482	0.891037	1.054482	1.001757	1.022847	1.159930	1.244288
91	Saline	0.931848	1.002621	1.065531	1.038008	0.986894	1.238532	1.281782
92	Schuyler	1.134615	0.884615	0.980769	1.115385	0.961538	1.153846	1.230769
93	Scotland	1.184211	1.042105	0.773684	0.647368	0.742105	0.963158	1.073684
94	Scott	0.973160	1.060583	0.966258	1.078988	1.058282	1.295245	1.341258
95	Shannon	0.938182	0.970909	1.090909	0.981818	1.341818	1.538182	1.309091
96	Shelby	0.959108	0.947955	1.092937	0.747212	0.836431	1.081784	0.970260
97	St. Charles	0.959329	0.992542	1.048130	1.100571	1.092530	1.325720	1.280270
98	St. Clair	1.061538	0.995604	0.942857	0.771429	0.936264	1.087912	1.008791
99	St. Francois	1.006027	0.964701	1.029272	1.043478	1.009901	1.256565	1.327594
100	St. Louis City	0.976737	0.992867	1.030396	0.999122	0.994843	1.206847	1.126852
101	St. Louis County	0.989251	0.992426	1.018323	1.079147	1.023284	1.240483	1.128163
102	Ste. Genevieve	1.013652	0.982935	1.003413	1.105802	0.936860	1.054608	1.264505
103	Stoddard	1.064914	0.944125	0.990961	1.003287	0.949055	1.119145	1.106820
104	Stone	0.920716	1.033248	1.046036	1.135550	1.084399	1.161125	1.273657
105	Sullivan	0.980198	0.846535	1.173267	1.232673	1.009901	1.247525	1.292079
106	Taney	0.994609	0.954178	1.051213	0.970350	0.965499	1.161186	1.287332
107	Texas	1.038835	0.961165	1.000000	1.071197	1.067961	1.275081	1.423948
108	Vernon	1.068921	0.924577	1.006502	0.924577	0.947984	1.096229	1.322497
109	Warren	0.951701	0.987925	1.060373	1.066959	0.974753	1.228321	1.524698
110	Washington	0.971366	0.994493	1.034141	0.901982	1.007709	1.229075	1.314978
111	Wayne	1.020513	0.958974	1.020513	1.092308	0.810256	1.143590	1.241026
112	Webster	1.027992	0.944015	1.027992	1.138031	1.059846	1.259653	1.468147
113	Worth	0.902913	1.077670	1.019417	0.728155	0.815534	0.844660	0.932039
114	Wright	0.842246	0.986631	1.171123	1.090909	0.986631	0.930481	1.171123

	date	count
0	2008-06-01	5931
1	2008-07-01	6376
2	2008-08-01	6413
3	2008-09-01	6543
4	2008-10-01	6284

Fred And The Vaccines¶

Setup¶

Missouri Department of Health & Senior Services¶

Getting the Data¶

HeatMap - Normalized Deaths 2015-2021 of Missouri Countys (based on Year)¶

Conclusion...¶

ChatGPT¶

But Wait There is More!¶

Fred of St Louis - Civilian Labor Force - With a Disability Data¶

Getting the Data¶

Civilian Labor Force - With a Disability, 16 Years and over¶

min & max lines calculated with values before 2020-01-01¶

Hypothesis¶

News (ksdk)¶

ChatGPT¶

Possible - Conclusion¶

if you want to keep digging¶